﻿/***********************************************************************************************************************************
 *    Name: Tables_Get-Sizes.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE master;
IF OBJECT_ID(N'TempDB..##TableSize') IS NOT NULL DROP TABLE ##TableSize;
CREATE TABLE ##TableSize (
	[Database]			NVARCHAR(128),
	[Schema]			NVARCHAR(255),
	[Object]			NVARCHAR(255),
	[Object Type]		NVARCHAR(60),
	[# Rows]			BIGINT,
	[Compression Type]	NVARCHAR(60),
	[Used (GB)]			DECIMAL(8,3),
	[Total (GB)]		DECIMAL(8,3));

DECLARE
  @tsql_command		NVARCHAR(MAX),
  @tsql_template	NVARCHAR(MAX)= N'USE [?];
WITH
  p (object_id, partition_id, rows, data_compression_desc) AS (SELECT object_id, partition_id, rows, data_compression_desc FROM sys.partitions WHERE index_id IN (0,1)),
  au (container_id, used_pages, total_pages) AS (SELECT container_id, SUM(used_pages), SUM(total_pages) FROM sys.allocation_units GROUP BY container_id)
	SELECT
		[Database]			= DB_NAME(),
		[Schema]			= SCHEMA_NAME(o.schema_id),
		[Object]			= o.name,
		[Object Type]		= o.type_desc,
		[# Rows]			= p.rows,
		[Compression Type]	= p.data_compression_desc,
		[Used (GB)]			= CAST((au.used_pages )/131072.0 AS FLOAT),
		[Total (GB)]		= CAST((au.total_pages)/131072.0 AS FLOAT)
	FROM sys.objects o
	  INNER JOIN p ON (o.object_id = p.object_id)
	  INNER JOIN au ON (p.partition_id = au.container_id)
	WHERE o.is_ms_shipped = 0 AND 255 < o.object_id
--	ORDER BY au.total_pages DESC, p.rows DESC, o.schema_id, o.name;';

DECLARE list CURSOR LOCAL FORWARD_ONLY FOR SELECT tsql_command= REPLACE(@tsql_template, N'?', database_name) FROM DBAdmin.DBList(N'@');
OPEN list;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @tsql_command;
  IF @@FETCH_STATUS <> 0 BREAK;
--PRINT @tsql_command;
  INSERT INTO ##TableSize EXECUTE (@tsql_command);
END;
CLOSE list;
DEALLOCATE list;
SELECT * FROM ##TableSize ORDER BY [Total (GB)] DESC, [# Rows] DESC, [Database], [Schema], [Object];
GO